<?php
/**
 * @description : operation of database.
 * @class-name : Model()
 * @author : stephen.mo
 * @date : 05/18/2010
 */

class Model {

    private $config;
    private $reader;
    private $writer;
    
    //instance
    static public function getInstance() {
        static $instance = null ;
        return $instance ? ($instance) : ($instance = new Model()) ;
    }
    
    //construct
    public function __construct() {
        $this->reader = DBConnPoolFactory::getInstance()->getConnPool(ModelConfig::DATABASE_CONFIG_PATH)->getReadConn();
        $this->writer = DBConnPoolFactory::getInstance()->getConnPool(ModelConfig::DATABASE_CONFIG_PATH)->getWriteConn();
    }

   
    //check the table's record is exist.
    public function getLastModifyTime($table_name,$now_time) {
        $result = array();
        $query = "SELECT id,day_time,json_data,last_modify_time FROM ".$table_name." WHERE day_time='".$now_time."' ";
        $ps = $this->writer->prepare($query);
        $ret = $ps->execute();
        if (!$ret) {
            //write error log.
            return false;
        }
        $result = ($ps->rowCount()>0) ? $ps->fetch(PDO::FETCH_ASSOC) : false;
        return $result;
    }

    //get the table's records.
    //get the base data from vdieo_access_log_temp for video viewing.
    public function getDataOfVideoViewing($start_time=null,$end_time=null) {
        $result = array();
        if (!empty($start_time) && !empty($end_time)) {
            $current_time_start = $start_time;
            $current_time_end = $end_time;
        }
        else {
            $one_hour = 60*60;
            $current_time_end = date('Y-m-d H:m:s');
            $current_time_start = date('Y-m-d H:m:s',strtotime($current_time_end)-$one_hour);
        }

        $query = "SELECT id,member_id,video_id,video_title,programme_id,programme_name,category_path,".
                 "country_code,gender,age_group,play_time,content_type,package_id,package_version,last_alive_time ".
                 " FROM ".ModelConfig::getTableNameOfVideoViewing().
                 " WHERE live=0 AND play_time>='".$current_time_start."' AND play_time<='".$current_time_end."' ".
                 " ORDER BY last_alive_time ASC ";
        $ps = $this->writer->prepare($query);
        $ret = $ps->execute();
        if (!$ret) {
            //write error log.
            return false;
        }
        $result = ($ps->rowCount()>0) ? $ps->fetchAll(PDO::FETCH_ASSOC) : $result;
        return $result;
    }

    //get base data from video_access_log_temp for video bitrate.
    public function getDataOfVideoBitrate($start_time=null,$end_time=null) {
        $result = array();
        if (!empty($start_time) && !empty($end_time)) {
            $current_time_start = $start_time;
            $current_time_end = $end_time;
        }
        else {
            $one_hour = 60*60;
            $current_time_end = date('Y-m-d H:m:s');
            $current_time_start = date('Y-m-d H:m:s',strtotime($current_time_end)-$one_hour);
        }
       
        $query = "SELECT count(id) as Total,play_time,country_code,bitrate,last_modify_time ".
                 " FROM ".ModelConfig::getTableNameOfVideoBitrate().
                 " WHERE live=0 AND play_time>='".$current_time_start."' AND play_time<='".$current_time_end."' ".
				 " GROUP BY last_modify_time,country_code,bitrate ". 
                 " ORDER BY last_modify_time ASC ";
		/**
		$query = "SELECT play_time,last_modify_time,count(id) as Total,country_code,bitrate".
				 " FROM ".ModelConfig::getTableNameOfVideoBitrate().
                 " WHERE play_time>='".$current_time_start."' AND play_time<='".$current_time_end."' ".
				 " GROUP BY bitrate,country_code ".
				 " ORDER BY last_modify_time ASC ";
		*/
        $ps = $this->writer->prepare($query);
        $ret = $ps->execute();
        if (!$ret) {
            //write error log.
            return false;
        }
        $result = ($ps->rowCount()>0) ? $ps->fetchAll(PDO::FETCH_ASSOC) : $result;
        return $result;
    }
    
    //get base data from fp_video_access_temp for OS and browser.
    public function getDataOfOSAndBrowser($start_time=null,$end_time=null) {
        $result = array();
        if (!empty($start_time) && !empty($end_time)) {
            $current_time_start = $start_time;
            $current_time_end = $end_time;
        }
        else {
            $one_hour = 60*60;
            $current_time_end = date('Y-m-d H:m:s');
            $current_time_start = date('Y-m-d H:m:s',strtotime($current_time_end)-$one_hour);
        }

        $query = "SELECT id,access_datetime,country_code,user_agent ".
                 "FROM ".ModelConfig::getTableNameOfFpVideoAccess().
                 " WHERE access_datetime>='".$current_time_start."' AND access_datetime<='".$current_time_end."' ".
                 " ORDER BY access_datetime ASC ";
        $ps = $this->writer->prepare($query);
        $ret = $ps->execute();
        if (!$ret) {
            //write error log.
            return false;
        }
        $result = ($ps->rowCount()>0) ? $ps->fetchAll(PDO::FETCH_ASSOC) : $result;
        return $result;
    }

    //get the hour data from hour table.
    //video viewing.
    public function getHourDataOfVideoViewing($start_time=null,$end_time=null) {
        $result = array();
        if (!empty($start_time) && !empty($end_time)) {
            $current_time_start = $start_time;
            $current_time_end = $end_time;
        }
        else {
            $one_day = 60*60*24;
            $to_day = date('Y-m-d');
            $current_time_start = date('Y-m-d 00:00:00',strtotime($to_day)-$one_day);
            $current_time_end = date('Y-m-d 23:00:00',strtotime($to_day)-$one_day);
            
        }

        $query = "SELECT id,day_time,json_data,last_modify_time ".
                 "FROM ".ModelConfig::getHourTableNameOfVideoViewing().
                 " WHERE day_time >= '".$current_time_start."' AND day_time <= '".$current_time_end."' ".
                 " ORDER BY day_time ASC ";
        $ps = $this->writer->prepare($query);
        $ret = $ps->execute();
        if (!$ret) {
            //write error log.
            return false;
        }
        $result = ($ps->rowCount()>0) ? $ps->fetchAll(PDO::FETCH_ASSOC) : $result;
        return $result;
    }
    
    //video bitrate.
    public function getHourDataOfVideoBitrate($start_time=null,$end_time=null) {
        $result = array();
        if (!empty($start_time) && !empty($end_time)) {
            $current_time_start = $start_time;
            $current_time_end = $end_time;
        }
        else {
            $one_day = 60*60*24;
            $to_day = date('Y-m-d');
            $current_time_start = date('Y-m-d 00:00:00',strtotime($to_day)-$one_day);
            $current_time_end = date('Y-m-d 23:00:00',strtotime($to_day)-$one_day);
        }

        $query = "SELECT id,day_time,json_data,last_modify_time ".
                 "FROM ".ModelConfig::getHourTableNameOfVideoBitrate().
                 " WHERE day_time >= '".$current_time_start."' AND day_time <= '".$current_time_end."' ".
                 " ORDER BY day_time ASC ";
        $ps = $this->writer->prepare($query);
        $ret = $ps->execute();
        if (!$ret) {
            //write error log.
            return false;
        }
        $result = ($ps->rowCount()>0) ? $ps->fetchAll(PDO::FETCH_ASSOC) : $result;
        return $result;
    }

    //OS & Browser.
    public function getHourDataOfOSAndBrowser($start_time=null,$end_time=null) {
        $result = array();
        if (!empty($start_time) && !empty($end_time)) {
            $current_time_start = $start_time;
            $current_time_end = $end_time;
        }
        else {
            $one_day = 60*60*24;
            $to_day = date('Y-m-d');
            $current_time_start = date('Y-m-d 00:00:00',strtotime($to_day)-$one_day);
            $current_time_end = date('Y-m-d 23:00:00',strtotime($to_day)-$one_day);
        }

        $query = "SELECT id,day_time,json_data,last_modify_time ".
                 "FROM ".ModelConfig::getHourTableNameOfOSAndBrowser().
                 " WHERE day_time >= '".$current_time_start."' AND day_time <= '".$current_time_end."' ".
                 " ORDER BY day_time ASC ";
        $ps = $this->writer->prepare($query);
        $ret = $ps->execute();
        if (!$ret) {
            //write error log.
            return false;
        }
        $result = ($ps->rowCount()>0) ? $ps->fetchAll(PDO::FETCH_ASSOC) : $result;
        return $result;
    }

    //hour datas to table.
    //update the record for hour table.
    public function updateHourTableRecord($table_name,$new_data,$id,$last_time) {
        $query = "UPDATE ".$table_name.
                 " SET json_data=:json_data,last_modify_time=:last_modify_time ".
                 " WHERE id=:id ";
        $ps = $this->writer->prepare($query);
        $ps->BindValue(':json_data',$new_data);
        $ps->BindValue(':last_modify_time',$last_time);
        $ps->BindValue(':id',$id);
        $ret = $ps->execute();
        if (!$ret) {
            //write log
            return false;
        }
        return true;
    }

    //add new record to hour table.
    public function insertHourTableRecord($table_name,$data,$last_modify_time,$day_time) {
        $query = "INSERT INTO ".$table_name.
                 " (id,day_time,json_data,last_modify_time) ".
                 " VALUES ".
                 " (0,:day_time,:json_data,:last_modify_time) ";
        $ps = $this->writer->prepare($query);
        $ps->BindValue(':day_time',$day_time);
        $ps->BindValue(':json_data',$data);
        $ps->BindValue(':last_modify_time',$last_modify_time);
        $ret = $ps->execute();
        if (!$ret) {
            //write log.
            return false;
        }
        return true;
    }

    //day datas to table.
    //update the record for day table.
    public function updateDayTableRecord($table_name,$new_data,$id,$last_time) {
        $query = "UPDATE ".$table_name.
                 " SET json_data=:json_data,last_modify_time=:last_modify_time ".
                 " WHERE id=:id ";
        $ps = $this->writer->prepare($query);
        $ps->BindValue(':id',$id);
        $ps->BindValue(':json_data',$new_data);
        $ps->BindValue(':last_modify_time',$last_time);
        $ret = $ps->execute();
        if (!$ret) {
            //write error log.
            return false;
        }

        return true;
    }
    
    //add new record to day table.
    public function insertDayTableRecord($table_name,$data,$last_modify_time,$day_time) {
        $query = "INSERT INTO ".$table_name.
                 " (id,day_time,json_data,last_modify_time) ".
                 " VALUES ".
                 " (0,:day_time,:json_data,:last_modify_time) ";
        $ps = $this->writer->prepare($query);
        $ps->BindValue(':day_time',$day_time);
        $ps->BindValue(':json_data',$data);
        $ps->BindValue(':last_modify_time',$last_modify_time);
        $ret = $ps->execute();
        if (!$ret) {
            //write error log.
            return false;
        }
        return true;
    }
    
    //remove the expire data.
    public function DeleteExpireOfData($date_condition,$table_flag) {
        $table_name = ModelConfig::$$table_flag;
        if (empty($table_name)) return false;
        $query = "DELETE FROM ".$table_name." WHERE 1 ".$date_condition;
        $ps = $this->writer->prepare($query);
        $ret = $ps->execute();
        if (!$ret) {
            return false;
        }
        return true;
    }

    //get the browser list.
    static public function getBrowserList() {
        $list = array();
        $config = ConfigReaderFactory::getInstance()->getConfigReader(ModelConfig::XML_PATH_OF_BROWSER)->getConfig();
        foreach($config->browsers as $key=>$value) {
            foreach($value->browser as $b_k=>$b_v) {
                $name = (string)$b_v->attributes()->name;
				$combina = (!empty($b_v->attributes()->combina)) ? (string)$b_v->attributes()->combina : 'OR';
                $pattern = array();
                foreach($b_v as $p_k=>$p_v) {
                    $pattern[] = (string)$p_v->attributes()->value;
                }
				$list[$name]['combina'] = $combina;
                $list[$name]['pattern'] = $pattern;
            }
        }
        return $list;
    }
    
    //get the OS list.
    static public function getOSList() {
        $list = array();
        $config = ConfigReaderFactory::getInstance()->getConfigReader(ModelConfig::XML_PATH_OF_OS)->getConfig();
        foreach($config->items as $key=>$value) {
            foreach($value as $o_k=>$o_v) {
				$name = (string)$o_v->attributes()->name;
				$combina = (!empty($o_v->attributes()->combina)) ? (string)$o_v->attributes()->combina : 'OR';
				$pattern = array();
				foreach($o_v as $p_k=>$p_v) {
					$pattern[] = (string)$p_v->attributes()->value;
				}
				$list[$name]['combina'] = $combina;
				$list[$name]['pattern'] = $pattern;
			}
        }
        
        return $list;
    }
    
    //daily summary part.
    public function getUserMailList() {
        $result = array();
        $query = "SELECT name,email FROM ".ModelConfig::getTableNameOfSendMail()." WHERE 1 ";
        $ps = $this->writer->prepare($query);
        $ret = $ps->execute();
        if (!$ret) {
            return $result;
        }
        $result = ($ps->rowCount()>0) ? $ps->fetchAll(PDO::FETCH_ASSOC) : $result;
        return $result;
    }

    public function checkMethodIsExists($name) {
        return method_exists($this,$name);
    }

    public function getDailySummaryData($date) {
        $data = array();
        $data['View Frequency'] = $this->getViewFrequencyData($date);
        $data['Total Streaming Duration'] = $this->getTotalDurationWithBitrate($date);
        $data['Number of unique user'] = $this->getUniqueUserData($date);
        $data['Subscriber with package'] = $this->getSubscriberPackageData($date);
        $data['Number of Errors'] = $this->getNumberOfErrors($date);
        return $data;
    }

    public function getViewFrequencyData($date) {
        $table_name = ModelConfig::getTableNameOfVideoAccess();
        //the date more than one month,choose the month table.
        if (strtotime(ModelConfig::getToday())-strtotime($date)>=30*24*60*60) {
            $current_date = date('Y_m',strtotime($date));
            $table_name = ModelConfig::getTablePrefixName($current_date);
        }
        $query = "SELECT count(*) AS Total FROM ".$table_name." WHERE SUBSTRING(play_time,1,10)='".$date."' ";
        $ps = $this->writer->prepare($query);
        $ret = $ps->execute();
        if (!$ret) {
            return false;
        }
        $data = ($ps->rowCount()>0) ? $ps->fetchAll(PDO::FETCH_ASSOC) : false ;
        return (isset($data[0]['Total'])) ? $data[0]['Total'] : 0;
    }
    
    public function getTotalDurationWithBitrate($date) {
        $table_name = ModelConfig::getTableNameOfVideoAccess();
        //the date more than one month,choose the month table.
        if (strtotime(ModelConfig::getToday())-strtotime($date)>=30*24*60*60) {
            $current_date = date('Y_m',strtotime($date));
            $table_name = ModelConfig::getTablePrefixName($current_date);
        }
        $query = "SELECT bitrate,count(id) AS Total FROM ".$table_name.
                 " WHERE SUBSTRING(play_time,1,10)='".$date."' ".
                 " GROUP BY bitrate  ".
                 " ORDER BY bitrate ASC ";
        $ps = $this->writer->prepare($query);
        $ret = $ps->execute();
        if (!$ret) {
            return false;
        }

        $data = ($ps->rowCount()>0) ? $ps->fetchAll(PDO::FETCH_ASSOC) : false ;
        return (!empty($data)) ? $data : ModelConfig::$default_bitrate_null;
    }

    public function getUniqueUserData($date) {
        $table_name = ModelConfig::getTableNameOfVideoAccess();
        //the date more than one month,choose the month table.
        if (strtotime(ModelConfig::getToday())-strtotime($date)>=30*24*60*60) {
            $current_date = date('Y_m',strtotime($date));
            $table_name = ModelConfig::getTablePrefixName($current_date);
        }
        $query = "SELECT DISTINCT member_id FROM ".$table_name.
                 " WHERE SUBSTRING(play_time,1,10)='".$date."' ";
        $ps = $this->writer->prepare($query);
        $ret = $ps->execute();
        if (!$ret) {
            return false ;
        }

        return $ps->rowCount();
    }

    public function getSubscriberPackageData($date) {
        $table_name = ModelConfig::getTableNameOfSubscription();
        $query = "SELECT package_id,type FROM ".$table_name.
                 " WHERE SUBSTRING(order_datetime,1,10)='".$date."' ".
                 " GROUP BY order_id,package_id,package_version ";
        $ps = $this->writer->prepare($query);
        $ret = $ps->execute();
        if (!$ret) {
            return false ;
        }
        
        $data = ($ps->rowCount()>0) ? $ps->fetchAll(PDO::FETCH_ASSOC) : array();
        //
        $result = array();
        foreach($data as $key=>$list ) {
            $package_id = $list['package_id'];
            $package_type = $list['type'];
            if (!isset($result[$package_id])) {
                $result[$package_id] = array();
                $result[$package_id]['total'] = 0;
                $result[$package_id]['new'] = 0;
                $result[$package_id]['renew'] = 0;
                $result[$package_id]['churn'] = 0;
            }
            $result[$package_id]['total'] ++;
            if ($package_type==1) {
                $result[$package_id]['new'] ++;
            }
            elseif($package_type==2) {
                $result[$package_id]['renew'] ++;
            }
            elseif($package_type==3) {
                $result[$package_id]['churn'] ++;
            }
        }
        
        return $result;
    }

    public function getNumberOfErrors($date) {
        return 0 ;
    }
    
    //stat footprint video viewing and favour nums.
    public function checkIsExistRecordStat($programme_id,$video_id,$category_id) {
        $table_name = ModelConfig::getTableNameOfFpVideoAccessWsStat();
        $query = "SELECT id FROM ".$table_name." WHERE programme_id=".$programme_id." AND video_id=".$video_id." AND category_id=".$category_id;
        $ps = $this->writer->prepare($query);
        $ret = $ps->execute();
        if (!$ret ) {
            return false;
        }
        return ($ps->rowCount()>0) ? true : false;
    }

    public function getVideoViewingFavourStat($start_time_stamp,$end_time_stamp) {
        $start_time = date('Y-m-d H:i:s',$start_time_stamp);
        $end_time = date('Y-m-d H:i:s',$end_time_stamp);
        $table_name = ModelConfig::getTableNameOfFpVideoAccess();
        $query = "SELECT access_datetime,programme_id,video_id,category_id,count_type,count(id) as total ".
                 " FROM ".$table_name.
                 " WHERE access_datetime>='".$start_time."' ".
                 " AND access_datetime<='".$end_time."' ".
                 " GROUP BY programme_id,video_id,count_type ";
        $ps = $this->writer->prepare($query);
        $ret = $ps->execute();
        if (!$ret) {
            return false;
        }

        $data = ($ps->rowCount()>0) ? $ps->fetchAll(PDO::FETCH_ASSOC) : array();
        return $data;
    }

    public function setVideoViewingFavourStat($data) {
        if (!is_array($data)) return false;
        $table_name = ModelConfig::getTableNameOfFpVideoAccessWsStat();
        $now_time = date('Y-m-d H:i:s');
        foreach($data as $cid=>$c_val) {
            foreach($c_val as $programme_id=>$list) {
                foreach($list as $video_id=>$val) {
                    //check this record is exists.
                    $result = $this->checkIsExistRecordStat($programme_id,$video_id,$cid);
                    $tmp_category = "category_id=".$cid.",";
                    $tmp_programme = "programme_id=".$programme_id.",";
                    $tmp_new_script = "video_id=".$video_id.",";
                    $tmp_last_modify_time = "last_modify_time='".$now_time."',";
                   
                    if ($result) {
                        $tmp_set = null;
                        foreach($val as $field=>$item) {
                            $tmp_set .= $field."=".$item.",";
                        }
                        $tmp_set = substr($tmp_set,0,strlen($tmp_set)-1);
                        $tmp_condition = $tmp_category.$tmp_programme.$tmp_new_script;
                        $tmp_condition = substr($tmp_condition,0,strlen($tmp_condition)-1);
                        $tmp_update_condition = " AND ".str_replace(',',' AND ',$tmp_condition);
                        $query = "UPDATE ".$table_name." SET ".$tmp_last_modify_time.$tmp_set." WHERE 1 ".$tmp_update_condition;
                    }
                    else {
                        $tmp_insert_condition = null;
                        $tmp_insert_value = null;
                        foreach($val as $field=>$item) {
                            $tmp_insert_condition .= ",".$field;
                            $tmp_insert_value .= ",".$item;
                        }

                        $query = "INSERT INTO ".$table_name.
                                 " (id,video_id,programme_id,category_id".$tmp_insert_condition.",last_modify_time) ".
                                 " VALUES ".
                                 " (0,".$video_id.",".$programme_id.",".$cid.$tmp_insert_value.",'".$now_time."') ";
                    }
                    
                    echo $query."\n";
                    $ps = $this->writer->prepare($query);
                    $ret = $ps->execute();
                    if (!$ret) {
                        return false;
                    }
                }
            }
        }
        
        return true;
    }


}

?>
